package xin.nick;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.net.URL;
import java.util.Objects;

/**
 * @author Nick
 * @since 2022/7/5/005
 */
public class ExcelCalc {


    public static void main(String[] args) throws IOException {

        // 需要读取的文件
        String filePath = "demo_calc.xls";

        ExcelCalc excelCalc = new ExcelCalc();
        URL resource = excelCalc.getClass().getResource("/");
        excelCalc.getCalc(resource.getPath() + filePath);

    }

    public String getCalc(String filePath) throws IOException {
        // 这里的计算模板内容为
        // 计算公式:  A1+B1-C1+0.5
        // A4 为公式计算的单元格

        // 准备用于计算的值
        Double[] args = {10D,20D,50D};
        // 结果应该是 10 + 20 - 50 + 0.5 = -19.5

        // 读取工作簿
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        if (Objects.nonNull(workbook)) {
            // 读取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (Objects.nonNull(sheet)) {
                // 读取第一行
                Row row = sheet.getRow(0);
                if (Objects.nonNull(row)) {

                    // 设置输入值
                    for (int i = 0; i < args.length; i++) {
                        Double arg = args[i];
                        Cell cell = row.getCell(i);
                        if (Objects.isNull(cell)) {
                            cell = row.createCell(i);
                        }
                        cell.setCellValue(arg);
                    }

                    // 更新公式计算值
                    FormulaEvaluator eval=null;
                    if(workbook instanceof HSSFWorkbook) {
                        eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                    }
                    else if(workbook instanceof XSSFWorkbook) {
                        eval = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                    }

                    // 读取第四列
                    Cell cell = row.getCell(3);
                    // 获取计算公式
                    String cellFormula = cell.getCellFormula();
                    // 计算公式
                    eval.evaluateFormulaCell(cell);

                    double numericCellValue = cell.getNumericCellValue();
                    System.out.println("计算公式:  " + cellFormula);
                    System.out.println("计算值:  " + numericCellValue);
                    return String.valueOf(numericCellValue);

                }

            }
        }

        return "";
    }

}
